﻿-- ================================================
--作者:   王飞
--创建日期:   2012年5月16日
--修改日期：
--版本号：　V1.0
--描述:  分页查询用户信息
--所属数据库:PlayMessage
--备注: 
-- ================================================
--drop PROCEDURE [proc_SelectUsers]
create PROCEDURE [proc_SelectUsers]
	@PageSize int=15,
    @PageIndex int=1,
    @SortFieldName nvarchar(50)='ID', --排序字段
    @SortDirection INT=0,	         --排序方向,
	@TotalCount int output
AS
	
	declare @sql nvarchar(max), @SortFieldNameSortDirection nvarchar(50);
	set @SortFieldNameSortDirection=isnull(@SortFieldName,'ID')+ltrim(@SortDirection);
	set @sql=
	'
	SELECT * FROM 
		(SELECT 
			Id, UserName, TrueName, UserIntegral, UserEmail, 
			UserEmailState, UserMobile, UserMobileState, UserQQ, 
			UserMSN, UserMoney, UserLoginTime, UserRegisterTime, 
			UserUpdateTime, UserImgUrl, UserState, 
			UserLoginState,
			case '''+@SortFieldNameSortDirection+'''
				 		WHEN ''UserRegisterTime0'' THEN ROW_NUMBER() OVER (ORDER BY u.UserRegisterTime DESC) 
						WHEN ''UserRegisterTime1'' THEN ROW_NUMBER() OVER (ORDER BY u.UserRegisterTime) 
						WHEN ''ID0'' THEN ROW_NUMBER() OVER (ORDER BY u.ID DESC) 
						WHEN ''ID1'' THEN ROW_NUMBER() OVER (ORDER BY u.ID) 
						ELSE ROW_NUMBER() OVER (ORDER BY u.ID DESC)
						END AS Rn

		 FROM pm_users u
		 ) 
		as r
	WHERE r.Rn between (@PageIndex-1)*@PageSize+1 and @PageIndex*@PageSize;
	'
	exec sp_executesql @sql,N'@PageSize int, @PageIndex int',
	 @PageSize,@PageIndex

	select @TotalCount=COUNT(1) from pm_users u 
GO